﻿using System;
using System.Collections;
using System.Data;
using OfficeOpenXml.Table;
using Telerik.Web.UI;
using System.Web.UI;
using KPIS.GERP.GAMS.DAL;
using KPIS.GERP.GAMS.APP.App_UI.Commons;
using System.Collections.Generic;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class ReportUnitCost : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        D_BGM_MAS_Source source = new D_BGM_MAS_Source();
        D_UnitCost unitCost = new D_UnitCost();
        MailMerge mailMerge = new MailMerge();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                txtBgmYear.Text = source.GetCurrentBgmYear();
            }
        }

        protected void btnReport_Click(object sender, EventArgs e)
        {
            string strBgmYear = txtBgmYear.Text;
            Dictionary<string, string> dic = new Dictionary<string, string>();
            DataTable dtbProj = unitCost.GetProjectCost(strBgmYear);
            DataTable dtbSubAct = unitCost.GetSubActCost(strBgmYear);
            DataTable dtbDepre = unitCost.GetDepreCost(strBgmYear);

            #region Project
            int nRow = 4;
            int nColCount = 0;
            string strOldBgmExpSeq = "";
            bool bFound = false;

            dic.Add("A2", "ปี " + strBgmYear);
            List<string> mergeFieldRange = new List<string>();
            for (int i = 0; i < dtbProj.Rows.Count; i++)
            {
                var dr = dtbProj.Rows[i];
                string strBgmExpSeq = dr["bgm_exp_seq"].ToString();
                string strBgmExpCode = dr["bgm_exp_code"].ToString();
                string strBgmExpName = dr["bgm_exp_name"].ToString();
                string strBgmProjSeq = dr["bgm_proj_seq"].ToString();
                string strBgmProjCode = dr["bgm_proj_code"].ToString();
                double dBudgetAmount1 = main.ConvertNullDouble(dr["budget_amount1"]) ?? 0;
                double dBudgetAmount2 = main.ConvertNullDouble(dr["budget_amount2"]) ?? 0;
                double dSumBudgetAmount = dBudgetAmount1 + dBudgetAmount2;
                double dUsedAmount1 = main.ConvertNullDouble(dr["used_amount1"]) ?? 0;
                double dUsedAmount2 = main.ConvertNullDouble(dr["used_amount2"]) ?? 0;

                if (strOldBgmExpSeq != strBgmExpSeq)
                {
                    nRow++;
                    dic.Add("A" + nRow, strBgmExpName);
                    dic.Add("B" + nRow, dBudgetAmount1.ToString("#,##0.00"));
                    dic.Add("C" + nRow, dBudgetAmount2.ToString("#,##0.00"));
                    dic.Add("D" + nRow, dSumBudgetAmount.ToString("#,##0.00"));
                    strOldBgmExpSeq = strBgmExpSeq;
                }

                bFound = false;
                for (int j = 0; j < nColCount; j++)
                {
                    string strCol1 = mailMerge.GetExcelColumnName((j * 2) + 5);
                    string strCol2 = mailMerge.GetExcelColumnName((j * 2) + 6);
                    string strOldBgmProjSeq = dic[strCol1 + "1"];
                    
                    if (strOldBgmProjSeq == strBgmProjSeq)
                    {
                        bFound = true;
                        dic.Add(strCol1 + nRow, dUsedAmount1.ToString("#,##0.00"));
                        dic.Add(strCol2 + nRow, dUsedAmount2.ToString("#,##0.00"));
                        break;
                    }

                }

                if (!bFound)
                {
                    nColCount++;
                    string strCol1 = mailMerge.GetExcelColumnName(((nColCount - 1) * 2) + 5);
                    string strCol2 = mailMerge.GetExcelColumnName(((nColCount - 1) * 2) + 6);
                    dic.Add(strCol1 + "1", strBgmProjSeq);
                    dic.Add(strCol2 + "1", strBgmProjSeq);
                    dic.Add(strCol1 + "3", strBgmProjCode);
                    dic.Add(strCol1 + "4", "งปม.");
                    dic.Add(strCol2 + "4", "สถาบัน");

                    dic.Add(strCol1 + nRow, dUsedAmount1.ToString("#,##0.00"));
                    dic.Add(strCol2 + nRow, dUsedAmount2.ToString("#,##0.00"));
                    
                    mergeFieldRange.Add(strCol1 + "3:" + strCol2 + "3");
                }
            }
            #endregion

            #region JobSite
            nRow = 4;
            nColCount = 0;
            strOldBgmExpSeq = "";
            bFound = false;
            string strSheetName = "ต้นทุนหน่วยงาน!";

            dic.Add(strSheetName + "A2", "ปี " + strBgmYear);

            for (int i = 0; i < dtbSubAct.Rows.Count; i++)
            {
                var dr = dtbSubAct.Rows[i];
                string strBgmExpSeq = dr["bgm_exp_seq"].ToString();
                string strBgmExpCode = dr["bgm_exp_code"].ToString();
                string strBgmExpName = dr["bgm_exp_name"].ToString();
                string strBgmStartListSeq = dr["bgm_strat_list_seq"].ToString();
                string strBgmStartListName = dr["bgm_strat_list_name"].ToString();
                double dBudgetAmount1 = main.ConvertNullDouble(dr["budget_amount1"]) ?? 0;
                double dBudgetAmount2 = main.ConvertNullDouble(dr["budget_amount2"]) ?? 0;
                double dBudget2Amount1 = main.ConvertNullDouble(dr["budget2_amount1"]) ?? 0;
                double dBudget2Amount2 = main.ConvertNullDouble(dr["budget2_amount2"]) ?? 0;
                double dSumBudgetAmount = dBudgetAmount1 + dBudgetAmount2;
                double dUsedAmount1 = main.ConvertNullDouble(dr["used_amount1"]) ?? 0;
                double dUsedAmount2 = main.ConvertNullDouble(dr["used_amount2"]) ?? 0;

                if (strOldBgmExpSeq != strBgmExpSeq)
                {
                    nRow++;
                    dic.Add(strSheetName + "A" + nRow, strBgmExpName);
                    dic.Add(strSheetName + "B" + nRow, dBudgetAmount1.ToString("#,##0.00"));
                    dic.Add(strSheetName + "C" + nRow, dBudgetAmount2.ToString("#,##0.00"));
                    dic.Add(strSheetName + "D" + nRow, dSumBudgetAmount.ToString("#,##0.00"));
                    strOldBgmExpSeq = strBgmExpSeq;
                }

                bFound = false;
                for (int j = 0; j < nColCount; j++)
                {
                    string strCol1 = mailMerge.GetExcelColumnName((j * 2) + 5);
                    string strCol2 = mailMerge.GetExcelColumnName((j * 2) + 6);
                    string strOldJobsiteSeq = dic[strSheetName + strCol1 + "1"];

                    if (strOldJobsiteSeq == strBgmStartListSeq)
                    {
                        bFound = true;
                        dic.Add(strSheetName + strCol1 + nRow, dUsedAmount1.ToString("#,##0.00"));
                        dic.Add(strSheetName + strCol2 + nRow, dUsedAmount2.ToString("#,##0.00"));
                        break;
                    }
                }

                if (!bFound)
                {
                    nColCount++;
                    string strCol1 = mailMerge.GetExcelColumnName(((nColCount - 1) * 2) + 5);
                    string strCol2 = mailMerge.GetExcelColumnName(((nColCount - 1) * 2) + 6);
                    dic.Add(strSheetName + strCol1 + "1", strBgmStartListSeq);
                    dic.Add(strSheetName + strCol2 + "1", strBgmStartListSeq);
                    dic.Add(strSheetName + strCol1 + "2", (dBudget2Amount1 + dBudget2Amount2).ToString("#,##0.00"));
                    dic.Add(strSheetName + strCol1 + "3", strBgmStartListName);
                    dic.Add(strSheetName + strCol1 + "4", "งปม.");
                    dic.Add(strSheetName + strCol2 + "4", "สถาบัน");

                    dic.Add(strSheetName + strCol1 + nRow, dUsedAmount1.ToString("#,##0.00"));
                    dic.Add(strSheetName + strCol2 + nRow, dUsedAmount2.ToString("#,##0.00"));

                    mergeFieldRange.Add(strSheetName + strCol1 + "2:" + strCol2 + "2");
                    mergeFieldRange.Add(strSheetName + strCol1 + "3:" + strCol2 + "3");
                }
            }
            #endregion

            #region Depre
            //nRow += 2;
            //for (int i = 0; i < dtbDepre.Rows.Count; i++)
            //{
            //    var dr = dtbDepre.Rows[i];
            //    string strBgmExpSeq = dr["account_seq"].ToString();
            //    string strBgmExpCode = dr["account_code"].ToString();
            //    string strBgmExpName = dr["account_name"].ToString();
            //    string strJobSiteSeq = dr["job_site_seq"].ToString();
            //    string strJobSiteName = dr["job_site_name"].ToString();
            //    double dBudgetAmount1 = 0;// main.ConvertNullDouble(dr["dep_amount"]) ?? 0;
            //    double dBudgetAmount2 = 0;// main.ConvertNullDouble(dr["budget_amount2"]) ?? 0;
            //    double dSumBudgetAmount = dBudgetAmount1 + dBudgetAmount2;
            //    double dUsedAmount1 = main.ConvertNullDouble(dr["dep_amount"]) ?? 0;
            //    double dUsedAmount2 = 0;// main.ConvertNullDouble(dr["used_amount2"]) ?? 0;

            //    if (strOldBgmExpSeq != strBgmExpSeq)
            //    {
            //        nRow++;
            //        dic.Add(strSheetName + "A" + nRow, strBgmExpName);
            //        dic.Add(strSheetName + "B" + nRow, dBudgetAmount1.ToString("#,##0.00"));
            //        dic.Add(strSheetName + "C" + nRow, dBudgetAmount2.ToString("#,##0.00"));
            //        dic.Add(strSheetName + "D" + nRow, dSumBudgetAmount.ToString("#,##0.00"));
            //        strOldBgmExpSeq = strBgmExpSeq;
            //    }

            //    bFound = false;
            //    for (int j = 0; j < nColCount; j++)
            //    {
            //        string strCol1 = mailMerge.GetExcelColumnName((j * 2) + 5);
            //        string strCol2 = mailMerge.GetExcelColumnName((j * 2) + 6);
            //        string strOldJobsiteSeq = dic[strSheetName + strCol1 + "1"];

            //        if (strOldJobsiteSeq == strJobSiteSeq)
            //        {
            //            bFound = true;
            //            dic.Add(strSheetName + strCol1 + nRow, dUsedAmount1.ToString("#,##0.00"));
            //            dic.Add(strSheetName + strCol2 + nRow, dUsedAmount2.ToString("#,##0.00"));
            //            break;
            //        }
            //    }

            //    if (!bFound)
            //    {
            //        nColCount++;
            //        string strCol1 = mailMerge.GetExcelColumnName(((nColCount - 1) * 2) + 5);
            //        string strCol2 = mailMerge.GetExcelColumnName(((nColCount - 1) * 2) + 6);
            //        dic.Add(strSheetName + strCol1 + "1", strJobSiteSeq);
            //        dic.Add(strSheetName + strCol2 + "1", strJobSiteSeq);
            //        dic.Add(strSheetName + strCol1 + "3", strJobSiteName);
            //        dic.Add(strSheetName + strCol1 + "4", "งปม.");
            //        dic.Add(strSheetName + strCol2 + "4", "สถาบัน");

            //        dic.Add(strSheetName + strCol1 + nRow, dUsedAmount1.ToString("#,##0.00"));
            //        dic.Add(strSheetName + strCol2 + nRow, dUsedAmount2.ToString("#,##0.00"));

            //        mergeFieldRange.Add(strSheetName + strCol1 + "2:" + strCol2 + "2");
            //        mergeFieldRange.Add(strSheetName + strCol1 + "3:" + strCol2 + "3");
            //    }
            //}
            #endregion

            mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/UnitCost.xlsx")
                , string.Format("UnitCost-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic, mergeFieldRange);
        }
    }
}
